<?
	session_start();	
	include('conn.php');		
			
	$qry1 = "select societyname , year, 
		sum(Jan) Jan,sum(Feb) Feb,sum(mar) Mar,sum(apr) Apr,sum(may) May,sum(jun) Jun,
		sum(jul) Jul,sum(aug) Aug,sum(sep) Sep,sum(oct) Oct,sum(nov) Nov,sum(Dece) Dece,
		sum(Jan) +sum(Feb) +sum(mar) +sum(apr) + sum(may) + sum(jun) 
		+sum(jul) +sum(aug) +sum(sep) +sum(oct) + sum(nov) +sum(Dece) grandtotal

		 from
		(
		select societyname, year, 
		case when month = 1 then total else 0 end 'Jan',
		case when month = 2 then total else 0 end 'Feb',
		case when month = 3 then total else 0 end 'Mar',
		case when month = 4 then total else 0 end 'Apr',
		case when month = 5 then total else 0 end 'May',
		case when month = 6 then total else 0 end 'Jun',
		case when month = 7 then total else 0 end 'Jul',
		case when month = 8 then total else 0 end 'Aug',
		case when month = 9 then total else 0 end 'Sep',
		case when month = 10 then total else 0 end 'Oct',
		case when month = 11 then total else 0 end 'Nov',
		case when month = 12 then total else 0 end 'Dece'

		from
		(
		
select s.socId,s.societyname,Year( t.TerminationDate ) AS year, MONTH( t.TerminationDate ) month,
count(t.TerminationDate) total 
from STBtoCustomer t, society s
where t.custId in(select custId from  STBtoCustomer sc)
and s.socId in (select socId from Customer where custId = t.custId)


		GROUP BY s.societyname, Year( t.TerminationDate ) , MONTH( t.TerminationDate )
		) as r

		) as JJ
		where year is not null
		group by societyname, year
		order by societyname;
		";
		$qry = mysql_query($qry1);

		$data = array();
		$i=1;
		while($rows = mysql_fetch_array($qry))
		{
						
			$data[] = array("Sr_NO" => $i++,
				"soc" => $rows['societyname'],					 
				"year" => $rows['year'],	
				 "Jan" => $rows['Jan']*1,
				 "Feb" => $rows['Feb']*1,
				 "Mar" => $rows['Mar']*1,
				 "Apr" => $rows['Apr']*1,
				 "May" => $rows['May']*1,
				 "Jun" => $rows['Jun']*1,
				 "Jul" => $rows['Jul']*1,
				 "Aug" => $rows['Aug']*1,
				 "Sep" => $rows['Sep']*1,
				 "Oct" => $rows['Oct']*1,
				 "Nov" => $rows['Nov']*1,
				"Dec" => $rows['Dece']*1,
				 "grandtotal" => $rows['grandtotal']*1
			);
		}
		echo json_encode($data);
	
?>